import pandas as pd
import re
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity

# 数据加载
tb_data = pd.read_excel("C:/Desktop/tb.xlsx")
jd_data = pd.read_excel("C:/Desktop/jd.xlsx")

# 清理淘宝数据中的HTML标签
def clean_html_tags(text):
    return re.sub(r'<[^>]+>', '', text)

tb_data['title_cleaned'] = tb_data['title'].apply(lambda x: clean_html_tags(x) if pd.notna(x) else '')

# 清理京东数据中的价格
def extract_price(price):
    match = re.search(r'\d+\.?\d*', price)
    return float(match.group()) if match else None

jd_data['Price_cleaned'] = jd_data['Price'].apply(lambda x: extract_price(x) if pd.notna(x) else 0)

# 合并文本字段，确保处理NaN
tb_data['combined'] = tb_data['shopTitle'].fillna('') + " " + tb_data['title_cleaned'].fillna('')
jd_data['combined'] = jd_data['Shop'].fillna('') + " " + jd_data['Title'].fillna('')

# 使用TF-IDF向量化合并后的文本
vectorizer = TfidfVectorizer()
tfidf_tb = vectorizer.fit_transform(tb_data['combined'])
tfidf_jd = vectorizer.transform(jd_data['combined'])

# 计算余弦相似度
cosine_sim = cosine_similarity(tfidf_tb, tfidf_jd)

# 设置相似度阈值
threshold = 0.60

# 提取相似度高于阈值的店铺对
similar_shops = []
for i in range(cosine_sim.shape[0]):
    for j in range(cosine_sim.shape[1]):
        if cosine_sim[i][j] > threshold:
            similar_shops.append((tb_data.iloc[i]['shopTitle'], jd_data.iloc[j]['Shop'], cosine_sim[i][j]))

# 将相似店铺对转换为DataFrame
similar_shops_df = pd.DataFrame(similar_shops, columns=['Taobao Shop', 'JD Shop', 'Similarity'])

# 输出结果
print(similar_shops_df)